drop table TBL_TRADE_CT_UUDAI

drop table TBL_TRADE_DAILY_CT_UUDAI

declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
declare @date_end_month nvarchar(100)= (select [MAX_DATE_ENDMONTH] from [DATE_EM_REPORT])
declare @month nvarchar(100)=(select [MAX_MONTH] from [DATE_EM_REPORT])

SELECT DISTINCT A.BUSINESS_DATE,CONVERT(date, A.DIST_DATE)as DIST_DATE , A.MATDT,A.CIF,A.CUSTOMER_NAME,A.ACCTNO,A.CCY,SUM(A.BAL) AS TOTAL_BAL,
                A.BRANCH_CODE,B.BRANCH_NAME_SME,
                A.CT_UUDAI_VAY,TERM,TYPE ='LS_USD',A.DAO, MONTH(DIST_DATE) AS MONTH,SUM(BAL_QD) as TOTAL_BALQD,
                           B.ZONE_ID_SME into TBL_TRADE_DAILY_CT_UUDAI
FROM [server74].BICDATA.DBO.LNTB_DISBURSEMENT A
LEFT JOIN [server74].[BICDATA].[dbo].[BRANCH_CODE] B
ON a.BRANCH_CODE COLLATE DATABASE_DEFAULT =B.BRANCH_ID
WHERE A.CT_UUDAI_VAY in ( '07-Cho vay VND bao dam USD ko BH tygia','85-Cho vay VND bao dam USD co BH tygia','07-UU DAI VAY VND LAI SUAT NGOAI TE'
,'06-Uu dai cho vay xuat nhap khau', '08-VAY VND UU DAI CHO VAY XUAT NHAP KHAU' )
and    VALUE_DATE =@date_update
and ((a.segment<>'KHCN' and a.branch_code not in (select branch_code from ANHCP.CEO_PERFORMANCE.DBO.branchoff))
              or (a.segment is null and LEFT(a.sector,1)>1))
GROUP BY  A.BUSINESS_DATE, DIST_DATE , A.MATDT,A.CIF,A.CUSTOMER_NAME,A.ACCTNO,A.CCY,
                A.BRANCH_CODE, B.BRANCH_NAME_SME,
                A.CT_UUDAI_VAY,A.DAO,A.TERM, ZONE_ID_SME
ORDER BY A.CIF asc

select BUSINESS_DATE,CIF,CT_UUDAI_VAY,SUM(TOTAL_BALQD)  TOTAL_BAL_QD into TBL_TRADE_CT_UUDAI
 from TBL_TRADE_DAILY_CT_UUDAI
 group by BUSINESS_DATE,CIF,CT_UUDAI_VAY
 
 --select DISTINCT CT_UUDAI_VAY from TBL_TRADE_CT_UUDAI
 
 --select * from TBL_TRADE_DAILY_CT_UUDAI

 
